<?php
date_default_timezone_set("Asia/Shanghai");
//exit;
$token = isset($_GET['token']) ? $_GET['token'] : '';
if ($token!='gsdbest'){
	exit;
}
include 'functions.php';
//$conn=mysql_connect("localhost","root","");
include 'db.php'; //数据库连接
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />";

$start = isset($_POST['start']) ? trim($_POST['start']) : "2014-01-01";
$end = isset($_POST['end']) ? trim($_POST['end']) : "2014-02-01";
$date_start = strtotime($start); 
$date_end = strtotime($end);	

if ($date_end<=$date_start){
	echo "时间跨度不合理";
	exit;
}

if ($date_end - $date_start > 3600*24*62){
	echo "时间跨度太大，已被管理员禁止，如有需要请与管理员联系";
	exit;
}

//注册人数
$sql = "select count(*) as regnum from ych_user where add_time>={$date_start} and add_time<{$date_end}";
$rs = db_fetch_array($sql,$conn);
$regNum = $rs['regnum'];

//投资总人数
$sql = "select count(distinct user_id) as c from ych_borrow_tender";
$rs = db_fetch_array($sql,$conn);
$tenderCount = $rs['c'];

$sql = "select count(distinct user_id) as c from ych_yjb_tender where user_id not in (select distinct user_id from ych_borrow_tender)";
$rs = db_fetch_array($sql,$conn);
$yjbTendCount = $rs['c'];
$tenderCount = $tenderCount + $yjbTendCount;

//贷款总人数
$sql = "select count(distinct user_id) as b from ych_borrow";
$rs = db_fetch_array($sql,$conn);
$borrowCount = $rs['b'];


//该时间段内投资和贷款人
$sql = "SELECT COUNT(*) as num1 FROM ych_user WHERE user_id IN (SELECT user_id FROM ych_borrow_tender WHERE add_time>={$date_start} AND add_time<{$date_end})";
$rs1 = db_fetch_array($sql,$conn);
$touziren = $rs1['num1'];
$sql = "SELECT COUNT(*) as num1 FROM ych_user WHERE user_id IN (SELECT user_id FROM ych_yjb_tender WHERE add_time>={$date_start} AND add_time<{$date_end}) and user_id not in (SELECT user_id FROM ych_borrow_tender WHERE add_time>={$date_start} AND add_time<{$date_end})";
$rs1 = db_fetch_array($sql,$conn);
$touziren_y = $rs1['num1'];
$touziren = $touziren + $touziren_y;

$sql = "SELECT COUNT(*) as num2 FROM ych_user WHERE user_id IN (SELECT user_id FROM ych_borrow WHERE verify_time>={$date_start} AND verify_time<{$date_end});";
$rs1 = db_fetch_array($sql,$conn);
$daikuanren = $rs1['num2'];

//截止到查询结束时间总投资和贷款人
$sql = "SELECT COUNT(*) as num1 FROM ych_user WHERE user_id IN (SELECT user_id FROM ych_borrow_tender WHERE add_time<{$date_end})";
$rs1 = db_fetch_array($sql,$conn);
$touziren2 = $rs1['num1'];
$sql = "SELECT COUNT(*) as num1 FROM ych_user WHERE user_id in (SELECT user_id FROM ych_yjb_tender WHERE add_time<{$date_end}) and user_id not in(SELECT user_id FROM ych_borrow_tender WHERE add_time<{$date_end})";
$rs1 = db_fetch_array($sql,$conn);
$touziren21 = $rs1['num1'];
$touziren2 = $touziren2 + $touziren21;

$sql = "SELECT COUNT(*) as num2 FROM ych_user WHERE user_id IN (SELECT user_id FROM ych_borrow WHERE verify_time<{$date_end});";
$rs1 = db_fetch_array($sql,$conn);
$daikuanren2 = $rs1['num2'];

$sql = "select id,ftype,account,repayment_account,is_day,time_limit,time_limit_day,apr,funds from ych_borrow where id in(select DISTINCT borrow_id from ych_borrow_tender where add_time>={$date_start} and add_time<{$date_end})";

$list = db_fetch_arrays($sql,$conn);

$total_money = 0;
$total_lixi = 0;
$total_borrow = 0;
$tmp = array();

$m0 = $d7 = $d15 = $d20 = $m1 = $m3 = $m6 = $m9 = $m12 = $m18 = $m24 = $m36 = 0;
$b0 = $t7 = $t15 = $t20 = $b1 = $b3 = $b6 = $b9 = $b12 = $b18 = $b24 = $b36 = 0;
$jiangli = 0;

foreach($list as $key => $val){	
	$tmp_jiangli = round($val['account'] * $val['funds'] / 100,2);
	$jiangli = $jiangli + $tmp_jiangli;
	$total_lixi = $total_lixi + $val['repayment_account']-$val['account'];
	
	$sql = "select sum(account) as account from ych_borrow_tender where borrow_id='{$val['id']}' and add_time>={$date_start} and add_time<{$date_end}";
	//$row = $mysql->db_fetch_array($sql);
	$row = db_fetch_array($sql,$conn);
	$val['account'] = $row['account'];
	
	
	
	
	if($val['is_day']==1){
		$nh = $val['apr']+ ($val['funds']*30/$val['time_limit_day']*12);
	}else{
		$nh = $val['apr']+ ($val['funds']/$val['time_limit']*12);
	}
	$tmp[] = array(
		'nh' => $nh,
		'money' => $val['account']
	);
	if ($val['ftype']==5){
		$b0 = $b0 + 1;
		$m0 = $m0 + $val['account'];
	}elseif ($val['is_day']==1 && $val['time_limit_day']==7){
		$t7 = $t7 + 1;
		$d7 = $d7 + $val['account'];
	}elseif ($val['is_day']==1 && $val['time_limit_day']==15){
		$t15 = $t15 + 1;
		$d15 = $d15 + $val['account'];
	}elseif ($val['is_day']==1 && $val['time_limit_day']==20){
		$t20 = $t20 + 1;
		$d20 = $d20 + $val['account'];
	}elseif ($val['is_day']==0 && $val['time_limit']==1 && $val['ftype']!=5){
		$b1 = $b1 + 1;
		$m1 = $m1+$val['account'];
	}elseif ($val['is_day']==0 && $val['time_limit']==3){
		$b3 = $b3 + 1;
		$m3 = $m3+$val['account'];
	}elseif ($val['is_day']==0 && $val['time_limit']==6){
		$b6 = $b6 + 1;
		$m6 = $m6+$val['account'];
	}elseif ($val['is_day']==0 && $val['time_limit']==9){
		$b9 = $b9 + 1;
		$m9 = $m9+$val['account'];
	}elseif ($val['is_day']==0 && $val['time_limit']==12){
		$b12 = $b12 + 1;
		$m12 = $m12+$val['account'];
	}elseif ($val['is_day']==0 && $val['time_limit']==18){
		$b18 = $b18 + 1;
		$m18 = $m18+$val['account'];
	}elseif ($val['is_day']==0 && $val['time_limit']==24){
		$b24 = $b24 + 1;
		$m24 = $m24+$val['account'];
	}elseif ($val['is_day']==0 && $val['time_limit']==36){
		$b36 = $b36 + 1;
		$m36 = $m36+$val['account'];
	}
	
	
	$total_borrow = $total_borrow + 1;
	$total_money = $total_money + $val['account'];
}
$avg = 0;
foreach($tmp as $val){
	$b = $val['nh']/$total_money *$val['money'];
	$avg = $avg + $b;
}

/*
$ret[2] = array(
	'type' => date('m',$date_start).'月',
	'account' => round($total_money/10000,2),
	'interest' => round($total_lixi/10000,2),
	'apr' => round($avg,2),
);
*/

//易居宝标种分布
$sql = "select time_limit_day,sum(account) as summoney from ych_yjb_tender where add_time>={$date_start} and add_time<{$date_end} group by time_limit_day";
$yjb_day_list = db_fetch_arrays($sql,$conn);
$sql = "select time_limit_day,count(id) as totalnum from ych_yjb_tender where add_time>={$date_start} and add_time<{$date_end} group by time_limit_day";
$yjb_count_list = db_fetch_arrays($sql,$conn);

$yjbList = array();
foreach ($yjb_day_list as $v){
	$yjbList[$v['time_limit_day']] = $v['summoney'];
	$yjbList['summoney'] = $yjbList['summoney'] + $v['summoney'];
}
$yjbCount = array();
foreach ($yjb_count_list as $v){
	$yjbCount[$v['time_limit_day']] = $v['totalnum'];
	$yjbCount['totalnum'] = $yjbCount['totalnum'] + $v['totalnum'];
}



//投资分布数据
$sql = "select user_id,sum(account) as summoney from ych_borrow_tender where add_time>={$date_start} and add_time<{$date_end} group by user_id";
$list2 = db_fetch_arrays($sql,$conn);
$list2_1 = db_fetch_arrays($sql,$conn);
$sql = "select user_id,sum(account) as summoney from ych_yjb_tender where add_time>={$date_start} and add_time<{$date_end} group by user_id";
$list2_2 = db_fetch_arrays($sql,$conn);
$list2 = array();
foreach ($list2_1 as $v){
	$list2[$v['user_id']] = $v;
}
foreach ($list2_2 as $v){
	if (isset($list2[$v['user_id']])){
		$list2[$v['user_id']]['summoney'] = $list2[$v['user_id']]['summoney'] + $v['summoney'];
	}else{
		$list2[$v['user_id']] = $v;
	}
}



$tc100 = $tc50 = $tc10 = $tc1 = $tc0 = 0;
$ts100 = $ts50 = $ts10 = $ts1 = $ts0 = 0;
foreach ($list2 as $val){
	if ($val['summoney']>=1000000){
		$tc100 = $tc100 + 1;
		$ts100 = $ts100 + $val['summoney'];
	}elseif ($val['summoney']>=500000){
		$tc50 = $tc50 + 1;
		$ts50 = $ts50 + $val['summoney'];
	}elseif ($val['summoney']>=100000){
		$tc10 = $tc10 + 1;
		$ts10 = $ts10 + $val['summoney'];
	}elseif ($val['summoney']>=10000){
		$tc1 = $tc1 + 1;
		$ts1 = $ts1 + $val['summoney'];
	}else{
		$tc0 = $tc0 + 1;
		$ts0 = $ts0 + $val['summoney'];
	}
}


//借款分布数据
$sql = "select user_id,sum(account) as summoney from ych_borrow where verify_time>={$date_start} and verify_time<{$date_end} and ftype!=5 group by user_id";
$list3 = db_fetch_arrays($sql,$conn);
$bc100 = $bc50 = $bc10 = $bc1 = $bc0 = 0;
$bs100 = $bs50 = $bs10 = $bs1 = $bs0 = 0;
foreach ($list3 as $val){
	if ($val['summoney']>=1000000){
		$bc100 = $bc100 + 1;
		$bs100 = $bs100 + $val['summoney'];
	}elseif ($val['summoney']>=500000){
		$bc50 = $bc50 + 1;
		$bs50 = $bs50 + $val['summoney'];
	}elseif ($val['summoney']>=100000){
		$bc10 = $bc10 + 1;
		$bs10 = $bs10 + $val['summoney'];
	}elseif ($val['summoney']>=10000){
		$bc1 = $bc1 + 1;
		$bs1 = $bs1 + $val['summoney'];
	}else{
		$bc0 = $bc0 + 1;
		$bs0 = $bs0 + $val['summoney'];
	}
}


//省份-投资额分布
$sql = "SELECT p2.province,p3.name,SUM(p1.account) AS taccount,COUNT(p1.id) AS count FROM ych_borrow_tender AS p1 LEFT JOIN ych_user AS p2 ON p1.user_id=p2.user_id 
	LEFT JOIN ych_area AS p3 ON p2.province=p3.id
	GROUP BY p2.province ORDER BY taccount DESC";
$prolist = db_fetch_arrays($sql,$conn);


$sql = "SELECT p2.province,p3.name,SUM(p1.account) AS taccount,COUNT(p1.id) AS count FROM ych_borrow_tender AS p1 LEFT JOIN ych_user AS p2 ON p1.user_id=p2.user_id 
	LEFT JOIN ych_area AS p3 ON p2.province=p3.id 
	WHERE p1.add_time>={$date_start} AND p1.add_time<{$date_end} 
	GROUP BY p2.province ORDER BY taccount DESC";
$prolistByDate = db_fetch_arrays($sql,$conn);



?>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style>
table{border-right:1px solid gray;border-bottom:1px solid gray}
table td{border-left:1px solid gray;border-top:1px solid gray}
</style>
<form action="" method="post">
时间：<input type="text" name="start" value="<?php echo $start;?>"> 至 <input type="text" name="end" value="<?php echo $end;?>">时间格式(2013-01-01)
<input type="submit" value="查询">
</form>

<hr>
用户统计：<br>
<table border="0" cellspacing="0" cellpadding="0" width="80%">
	<tr>
		<td>该时间段注册人数</td>
		<td>当前投资总人数</td>
		<td>截止到查询结束时间投资总人数</td>
    	<td>该时间段投资人数</td>
    	<td>贷款总人数</td>
    	<td>截止到查询结束时间贷款总人数</td>
    	<td>该时间段贷款人数</td>
	</tr>
	<tr>
		<td><?php echo $regNum;?></td>
		<td><?php echo $tenderCount;?></td>
    	<td><?php echo $touziren2;?></td>
    	<td><?php echo $touziren;?></td>
    	<td><?php echo $borrowCount;?></td>
    	<td><?php echo $daikuanren2;?></td>
    	<td><?php echo $daikuanren;?></td>
	</tr>
</table><br><br>

标种分布数据(<?php echo $start . ' - ' . $end;?>)<br>
<table border="0" cellspacing="0" cellpadding="0" width="100%">
  <tr>
  	<td>分布</td>
    <td>总</td>
    <td>秒标</td>
    <td>7天标</td>
    <td>15天标</td>
    <td>20天标</td>
    <td>1月标</td>
    <td>3月标</td>
    <td>6月标</td>
    <td>9月标</td>
    <td>12月标</td>
    <td>18月标</td>
    <td>24月标</td>
    <td>36月标</td>
    <td>已获利息</td>
    <td>投标奖励</td>
    <td>平均年化率</td>
  </tr>
  <tr>
  	<td>成交金额</td>
    <td><?php echo $total_money;?></td>
    <td><?php echo $m0;?></td>
    <td><?php echo $d7;?></td>
    <td><?php echo $d15;?></td>
    <td><?php echo $d20;?></td>
    <td><?php echo $m1;?></td>
    <td><?php echo $m3;?></td>
    <td><?php echo $m6;?></td>
    <td><?php echo $m9;?></td>
    <td><?php echo $m12;?></td>
    <td><?php echo $m18;?></td>
    <td><?php echo $m24;?></td>
    <td><?php echo $m36;?></td>
    <td rowspan="2"><?php echo $total_lixi;?></td>
    <td rowspan="2"><?php echo $jiangli;?></td>
    <td rowspan="2"><?php echo round($avg,2);?>%</td>
  </tr>
  <tr>
  	<td>成交笔数</td>
    <td><?php echo $total_borrow;?></td>
    <td><?php echo $b0;?></td>
    <td><?php echo $t7;?></td>
    <td><?php echo $t15;?></td>
    <td><?php echo $t20;?></td>
    <td><?php echo $b1;?></td>
    <td><?php echo $b3;?></td>
    <td><?php echo $b6;?></td>
    <td><?php echo $b9;?></td>
    <td><?php echo $b12;?></td>
    <td><?php echo $b18;?></td>
    <td><?php echo $b24;?></td>
    <td><?php echo $b36;?></td>
  </tr>
</table><br><br>
易居宝按期限统计成交量(<?php echo $start . ' - ' . $end;?>)<br>
<table border="0" cellspacing="0" cellpadding="0" width="100%">
	<tr>
		<td>分布</td>
	    <td>总</td>
	    <td>7天</td>
	    <td>15天</td>
	    <td>20天</td>
	    <td>30天</td>
	</tr>
	<tr>
		<td>成交金额</td>
	    <td><?php echo $yjbList['summoney'];?></td>
	    <td><?php echo isset($yjbList['7']) ? $yjbList['7'] : 0;?></td>
	    <td><?php echo isset($yjbList['15']) ? $yjbList['15'] : 0;?></td>
	    <td><?php echo isset($yjbList['20']) ? $yjbList['20'] : 0;?></td>
	    <td><?php echo isset($yjbList['30']) ? $yjbList['30'] : 0;?></td>
	</tr>
	<tr>
		<td>成交笔数</td>
	    <td><?php echo $yjbCount['totalnum'];?></td>
	    <td><?php echo isset($yjbCount['7']) ? $yjbCount['7'] : 0;?></td>
	    <td><?php echo isset($yjbCount['15']) ? $yjbCount['15'] : 0;?></td>
	    <td><?php echo isset($yjbCount['20']) ? $yjbCount['20'] : 0;?></td>
	    <td><?php echo isset($yjbCount['30']) ? $yjbCount['30'] : 0;?></td>
	</tr>
</table><br><br>

投资分布数据(<?php echo $start . ' - ' . $end;?>)<br>
<table border="0" cellspacing="0" cellpadding="0" width="100%">
  <tr>
    <td>分布</td>
    <td>100万及100万以上</td>
    <td>50万及100万以下</td>
    <td>10万及50万以下</td>
    <td>1万及10万以下</td>
    <td>1万以下</td>
  </tr>
  <tr>
    <td>人数</td>
    <td><?php echo $tc100;?></td>
    <td><?php echo $tc50;?></td>
    <td><?php echo $tc10;?></td>
    <td><?php echo $tc1;?></td>
    <td><?php echo $tc0;?></td>
  </tr>
  <tr>
    <td>累计金额</td>
    <td><?php echo $ts100;?></td>
    <td><?php echo $ts50;?></td>
    <td><?php echo $ts10;?></td>
    <td><?php echo $ts1;?></td>
    <td><?php echo $ts0;?></td>
  </tr>
</table><br><br>

借款分布数据(<?php echo $start . ' - ' . $end;?>)<br>
<table border="0" cellspacing="0" cellpadding="0" width="100%">
  <tr>
    <td>分布</td>
    <td>100万及100万以上</td>
    <td>50万及100万以下</td>
    <td>10万及50万以下</td>
    <td>1万及10万以下</td>
    <td>1万以下</td>
  </tr>
  <tr>
    <td>人数</td>
    <td><?php echo $bc100;?></td>
    <td><?php echo $bc50;?></td>
    <td><?php echo $bc10;?></td>
    <td><?php echo $bc1;?></td>
    <td><?php echo $bc0;?></td>
  </tr>
  <tr>
    <td>累计金额</td>
    <td><?php echo $bs100;?></td>
    <td><?php echo $bs50;?></td>
    <td><?php echo $bs10;?></td>
    <td><?php echo $bs1;?></td>
    <td><?php echo $bs0;?></td>
  </tr>
</table><br><br>
<div style="float:left;">
<div style="float:left;">
借款分布数(总):
<table border="0" cellspacing="0" cellpadding="0" width="300px;">
  <tr>
    <td>省份</td>
    <td>投资人次</td>
    <td>投资金额</td>
  </tr>
  <?php foreach ($prolist as $v){?>
  <tr>
    <td><?php echo $v['name'] ? $v['name'] : '无';?></td>
    <td><?php echo $v['count'];?></td>
    <td><?php echo $v['taccount'];?></td>
  </tr>
  <?php }?>
</table>
</div>
<div style="float:left;margin-left:50px;">
借款分布数(<?php echo $start . ' - ' . $end;?>)
<table border="0" cellspacing="0" cellpadding="0" width="300px;">
  <tr>
    <td>省份</td>
    <td>投资人次</td>
    <td>投资金额</td>
  </tr>
  <?php foreach ($prolistByDate as $v){?>
  <tr>
    <td><?php echo $v['name'] ? $v['name'] : '无';?></td>
    <td><?php echo $v['count'];?></td>
    <td><?php echo $v['taccount'];?></td>
  </tr>
  <?php }?>
</table>
</div>
</div>